package dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;

import modelo.RelVendas;

public class DaoRelVendas {

    public ArrayList<RelVendas> consultarRelVendas(Date dataini, Date datafin) {
    	
    	ArrayList<RelVendas> servicos = new ArrayList<RelVendas>();
    	
      try {
        Class.forName("org.postgresql.Driver");
            Connection con = null;
             con = DriverManager.getConnection("jdbc:postgresql:Siafu","postgres", "postgresql123");
       if (con != null)
          {
                
         
    	   		  Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);

                  String query="select nome_plano, count(nome_plano), sum(val_plano) from venda, plano, beneficiario " +
                               " where dt_venda >= '" + dataini + "' and " +
                               " dt_venda <= '" + datafin + "' and " +                               
                               " venda.id_plano = plano.id_plano and " + 
                               " venda.id_venda = beneficiario.id_venda " +
                               "group by nome_plano; ";
                		  	   
                  ResultSet rs = stmt.executeQuery(query);
                  
                  while(rs.next()){
                	  RelVendas rel = new RelVendas();
                	  
                	    int cols = rs.getMetaData().getColumnCount();
                	    Object[] arr = new Object[cols];
                	    for(int i=0; i<cols; i++){
                	      arr[i] = rs.getObject(i+1);
                	      rel.setNome_plano(rs.getString(1));                	      
                	      rel.setQtd_planos(rs.getInt(2));
                	      rel.setVal_planos(rs.getFloat(3));
                	    }
                	    servicos.add(rel);
                	}
                  
          }
          else
              System.out.println("Connection Failed!");
      }
          catch(Exception e){
                              e.printStackTrace();
          }
      
    
      
      return servicos;
    
    }
    
    
}
